package ro.gateway.aida.obj.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import ro.gateway.aida.db.DBPersistenceManager;
import ro.gateway.aida.db.PersistenceToken;
import ro.gateway.aida.obj.AIDAActivityObject;
import ro.gateway.aida.obj.OrganizationRole;
import ro.gateway.aida.obj.PersonInv;
import ro.gateway.aida.srv.IIDGenerator;



/**
 * <p>Title: Romanian AIDA</p>
 * <p>Description: :D application</p>
 * <p>Copyright: Copyright (comparator) 2003</p>
 * <p>Company: Romania Development Gateway </p>
 * @author Mihai Popoaei, mihai_popoaei@yahoo.com, smike@intellisource.ro
 * @version 1.0-* @version $Id: PersonInvDB.java,v 1.1 2004/10/24 23:37:11 mihaipostelnicu Exp $
 */
public class PersonInvDB extends DBPersistenceManager
{
      private PersonInvDB( PersistenceToken token )
      {
            super( token );
      }

      public static PersonInvDB getManager( PersistenceToken token )
      {
            return new PersonInvDB( token );
      }

      public PersonInv[] getForActivity(
            long activityId )
            throws SQLException
      {

            PersonInv[] result = null;

            Connection con = getConnection();
            ArrayList items = new ArrayList();

            try
            {
                  PreparedStatement ps = con.prepareStatement( GET_IDS_AND_ROLES );
                  ps.setLong( 1, activityId );
                  ResultSet rs = ps.executeQuery();
                  while ( rs.next() )
                  {
                        PersonInv item = new PersonInv();
                        item.setPerson_id( rs.getLong( 1 ) );
                        int role_id = rs.getInt( 2 );
                        if ( rs.wasNull() )
                        {
                              role_id = -1;
                        }

                        item.role = OrganizationRole.getCachedByLocalId( new Integer( role_id ) );

                        items.add( item );
                  }
                  rs.close();
                  ps.close();
            }
            catch ( SQLException sqlEx )
            {
                  throw sqlEx;
            }
            finally
            {
                  con.close();
            }

            if ( items.size() > 0 )
            {
                  result = new PersonInv[ items.size() ];
                  items.toArray( result );
            }

            if ( result != null )
                  for ( int i = 0; i < result.length; i++ )
                  {
                        result[ i ].setPerson( PersonDB.getManager( token ).
                              getByID( result[ i ].getPerson_id() ) );
                  }

            return result;
      }

      public void delete(
            long id )
            throws SQLException
      {
            Connection con = getConnection();

            try
            {
                  PreparedStatement ps = con.prepareStatement( DELETE );
                  ps.setLong( 1, id );
                  ps.executeUpdate();
                  ps.close();
            }
            catch ( SQLException sqlEx )
            {
                  throw sqlEx;
            }
            finally
            {
                  con.close();
            }

      }

      public void deleteForActivity(
            long activity_id )
            throws SQLException
      {
            Connection con = getConnection();
            String database_pname = con.getMetaData().getDatabaseProductName();
            if ( 0 == database_pname.compareToIgnoreCase( "MYSQL" ) )
            {
                  System.out.println( "MYSQL - deleteForActivity (personsinvolved) -- DEBUG" );
                  con.close();
                  deleteForActivityMYSQL( activity_id );
                  return;
            }

            try
            {

                  PreparedStatement ps = con.prepareStatement( DELETE_FOR_ACTIVITY );
                  ps.setLong( 1, activity_id );
                  ps.executeUpdate();
                  ps.close();

                  AIDAActivityObjectDB.getManager( token ).deleteForActivityByType(
                        con, activity_id, AIDAActivityObject.TYPE_PERSON_INVOLVED );
            }
            catch ( SQLException sqlEx )
            {
                  throw sqlEx;
            }
            finally
            {
                  con.close();
            }

      }

      private void deleteForActivityMYSQL(

            long activity_id )
            throws SQLException
      {

            PersonInv[] pers = getForActivity( activity_id );

            if ( pers != null )
            {
                  Connection con = getConnection();
                  try
                  {
                        PreparedStatement ps = con.prepareStatement(
                              "DELETE FROM pers_inv WHERE id = ?" );
                        for ( int i = 0; i < pers.length; i++ )
                        {
                              ps.clearParameters();
                              ps.setLong( 1, pers[ i ].getId() );
                              ps.executeUpdate();
                        }
                        ps.close();
                        AIDAActivityObjectDB.getManager( token ).deleteForActivityByType(
                              con, activity_id, AIDAActivityObject.TYPE_PERSON_INVOLVED );
                  }
                  catch ( SQLException sqlEx )
                  {
                        throw sqlEx;
                  }
                  finally
                  {
                        con.close();
                  }
            }
      }

      public void insert(
            IIDGenerator idGenerators,
            PersonInv item )
            throws SQLException
      {

            AIDAActivityObjectDB.getManager( token ).insert( idGenerators, item );

            Connection con = getConnection();

            try
            {
                  PreparedStatement ps = con.prepareStatement( INSERT );

                  ps.setLong( 1, item.getId() );
                  ps.setLong( 2, item.getPerson_id() );
                  ps.setInt( 3, item.role.getId().intValue() );

                  ps.executeUpdate();
                  ps.close();
            }
            catch ( SQLException sqlEx )
            {
                  throw sqlEx;
            }
            finally
            {
                  con.close();
            }
      }

      private static final String GET_IDS_AND_ROLES =
            "SELECT pi.pers_id, pi.role " +
            "FROM pers_inv pi, objects o " +
            "WHERE o.id=pi.id AND activity_id = ?";

      private static final String DELETE =
            "DELETE FROM pers_inv WHERE id = ?";
      private static final String DELETE_FOR_ACTIVITY =
            "DELETE FROM pers_inv WHERE id IN (SELECT id FROM objects WHERE activity_id=?)";

      private static final String INSERT =
            "INSERT INTO pers_inv (id, pers_id, role) " +
            "VALUES (?,?,?)";
}
